﻿using MySql.Data.MySqlClient;
using NB.MGJ.Common.Utility;
using NB.MGJ.Model;
using NB.MGJ.Model.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NB.MGJ.FrameWork;

namespace NB.MGJ.Service
{
    /// <summary>
    /// 华君
    /// </summary>
    public partial class ManagerService
  
    {
        public List<AntiFleeing_SalesArea> GetSaleAreaList(string searchKey)
        {
            string _sql = string.Format("SELECT `Name`,Contacts,ContactTel,CreateDate FROM antifleeing_salesarea WHERE ViewDel=0   and id In({0})", searchKey);
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(_sql).ToList();
        }

        public PagedList<AntiFleeing_SalesArea> GetSaleAreaList(string searchKey, ref PagerModel pager, LogOnUserModel user)
        {
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("*");
            var strSql = new StringBuilder(strSql1.ToString().Trim(','));
            pager.FldName = strSql.ToString();
            pager.TblName = " ( select saa.*,oe.EnterpriseName,oe.ID AS EnterId,oe.SuperiorsOrganization from AntiFleeing_SalesArea saa left join  Organization_Enterprise oe  on saa.EnterpriseId=oe.ID " +
                 "where saa.ViewDel=0) w";
            pager.StrCondition = " EnterpriseId = " + user.EnterpriseId + " And ViewDel = 0  ";
            if (!string.IsNullOrWhiteSpace(searchKey))
            {
                pager.StrCondition += "  And  Name  LIKE  '%" + searchKey + "%'  ";
            }
            pager.FldSort = " CreateDate  desc";
            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);
            IEnumerable<AntiFleeing_SalesArea> resultList = _baseRepository.ExecuteStoredProcedureCollection<AntiFleeing_SalesArea>("Common_PageList", parameters);
            return new PagedList<AntiFleeing_SalesArea>(resultList, pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }


        public AntiFleeing_SalesArea GetSaleAreInfo(int id)
        {
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>("SELECT * FROM AntiFleeing_SalesArea WHERE ID = " + id).FirstOrDefault();
        }
        //微信小程序片区下拉框
        public IEnumerable<SelectItem> GetSalesAreaList()
        {
            string sql = " select ID,Name from AntiFleeing_SalesArea where Name is not NULL";
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_SalesArea>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.ID,
                    Name = p.Name
                };
            });
        }
        //微信小程序产品入库添加

        //public void StorageAdd(AntiFleeing_IOWarehouse model)
        //{

        //    _baseRepository.Insert<AntiFleeing_IOWarehouse>(model);
        //}
        //微信小程序调拨添加
        public void UnBoxingAdd(AntiFleeing_Allocation model)
        {
            _baseRepository.Insert<AntiFleeing_Allocation>(model);
        }

        public string UpdateSlaeArea(AntiFleeing_SalesArea model)
        {
            try
            {
                if (model != null)
                {
                    string _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                    using (MySqlConnection conn = new MySqlConnection(_connectionString))
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            conn.Open();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("SELECT `Name` FROM antifleeing_salesarea WHERE ID = {0}", model.ID);
                            string _name = Convert.ToString(cmd.ExecuteScalar());
                            if (model.Name != _name)
                            {
                                cmd.CommandText = string.Format("SELECT ID FROM antifleeing_salesarea WHERE EnterpriseId ={0} AND `Name`='{1}' AND ViewDel = 0", model.EnterpriseId, model.Name);
                                object _ex = cmd.ExecuteScalar();
                                if (_ex != null)
                                {
                                    return "1111";
                                }
                            }
                            //cmd.CommandText = new NB.MGJ.FrameWork.BaseExecute().GetUpdateSQL<AntiFleeing_SalesArea>(model).ToString();
                            string sql =string.Format( "update AntiFleeing_SalesArea set `Name`='{0}',`Contacts`='{1}',`ContactTel`='{2}',`Remark`='{3}',`EnterpriseId`='{4}',`ViewDel`='0',`ChangeLoginID`='{5}',`ChangeLoginName`='{6}',`ChangeDate`='{7}' where ID={8}",
                                                              model.Name,model.Contacts,model.ContactTel,model.Remark,model.EnterpriseId,model.ChangeLoginID,model.ChangeLoginName,model.ChangeDate,model.ID);
                           cmd.CommandText=sql;
                            cmd.ExecuteScalar();
                            return "0000";
                        }
                    }
                }
                else { return "4444"; }
            }
            catch { }
            return "4444";
        }


        public string AddSaleArea(AntiFleeing_SalesArea model)
        {
            try
            {
                if (model != null)
                {
                    var _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                    using (MySqlConnection conn = new MySqlConnection(_connectionString))
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            conn.Open();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = string.Format("SELECT ID FROM antifleeing_salesarea WHERE EnterpriseId ={0} AND `Name`='{1}' AND ViewDel = 0", model.EnterpriseId, model.Name);
                            object _ex = cmd.ExecuteScalar();
                            if (_ex == null)
                            {
                                string _insertSql = new NB.MGJ.FrameWork.BaseExecute().GetInsertSQL<AntiFleeing_SalesArea>(model).ToString();
                                if (!string.IsNullOrWhiteSpace(_insertSql))
                                {
                                    cmd.CommandText = _insertSql;
                                    cmd.ExecuteScalar();
                                }
                                return "0000";
                            }
                            else { return "1111"; }
                        }
                    }
                }{ return "4444"; }
            }
            catch { return "4444"; }
        }

        public string DeleteAreaManager(int id)
        {
            try
            {
                var _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("SELECT ID FROM antifleeing_distributor WHERE  ViewDel = 0 AND SalesAreaID ={0}", id);
                        object _id = cmd.ExecuteScalar();
                        if (_id != null)
                        {
                            return "4445";//已被使用，无法删除
                        }
                        cmd.CommandText = string.Format("UPDATE AntiFleeing_SalesArea  SET ViewDel = 1  WHERE ID ={0}", id);
                        cmd.ExecuteScalar();
                        return "0000";
                    }
                }
            }
            catch { return "4444"; }
        }

        #region 经销商
        public PagedList<V_Distributor> DiscGetPageList(ref PagerModel pager, string key)
        {
            DataTable result = new DataTable();

            pager.FldName = @" * ";
            pager.TblName = @" V_Distributor as w ";
            string StrCondition = " 1=1 ";
            if (string.IsNullOrWhiteSpace(pager.StrCondition))
            {
                StrCondition = " 1=1 ";
            }
            else
            {
                StrCondition = pager.StrCondition;
            }

            if (key != null && key != "")
            {
                StrCondition += " and  (w.Name like '%" + key + "%')";
            }

            pager.StrCondition = StrCondition;
            if (string.IsNullOrEmpty(pager.FldSort.Replace(" ", "")))
                pager.FldSort = " w.CreateDate desc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref pager);

            IEnumerable<V_Distributor> resultList = _baseRepository.ExecuteStoredProcedureCollection<V_Distributor>("Common_PageList", parameters);
            return new PagedList<V_Distributor>(resultList.ToList(), pager.Page, pager.PageSize, 1, Convert.ToInt32(parameters[6].Value));

        }


        //微信小程序经销商下拉框
        public IEnumerable<SelectItem> GetDistributorList(int areaId)
        {
            string sql = " select Id,Name from AntiFleeing_Distributor where  Name is NOT NULL and SalesAreaID=" + areaId;
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_Distributor>(sql).Select(p =>
            {
                return new SelectItem
                {
                    Code = p.Id,
                    Name = p.Name
                };
            });
        }

        public V_Distributor DealerShowDataId(int id)
        {
            string sql = "SELECT* FROM V_Distributor where ID=" + id;
            var result = _baseRepository.ExecuteSqlCollection<V_Distributor>(sql).FirstOrDefault();
            return result;
        }
        #region 导出查询
        public IEnumerable<V_Distributor> GetAllDealer(string key)
        {
            var rs = "SELECT * FROM V_Distributor ";
            if (!string.IsNullOrWhiteSpace(key))
            {
                rs += " where id In(" + key + ")";
            }
            return _baseRepository.ExecuteSqlCollection<V_Distributor>(rs);
        }
        #endregion

        public List<AntiFleeing_DistributorLevel> SelDiscLevel(int enterId)
        {

            // var strSql = "  select ButtonId,LevelName from AntiFleeing_DistributorLevel order by Level asc";
            string strSql = @" select * from AntiFleeing_DistributorLevel where ViewDel=0 and  EnterpriseId=@EnterpriseId";
            MySqlParameter[] parameters = {
                                        new MySqlParameter("@EnterpriseId",enterId)
                                        };
            IEnumerable<AntiFleeing_DistributorLevel> _query = _baseRepository.ExecuteSqlCollection<AntiFleeing_DistributorLevel>(strSql, parameters);
            return _query.ToList();
        }
        public List<AntiFleeing_DistributorLevel> SelDiscLevel(int dicId, int enterId)
        {

            // var strSql = "  select ButtonId,LevelName from AntiFleeing_DistributorLevel order by Level asc";
            string strSql = "  select * from AntiFleeing_DistributorLevel where EnterpriseId=" + enterId + " and ViewDel=0 and level>(  select Level from AntiFleeing_DistributorLevel where ID=(  select  LevelID from AntiFleeing_Distributor where ID=" + dicId + ")) order by Level asc";

            IEnumerable<AntiFleeing_DistributorLevel> _query = _baseRepository.ExecuteSqlCollection<AntiFleeing_DistributorLevel>(strSql, null);
            return _query.ToList();
        }
        public bool ExistDictributor(string name, string tel, int enterId, int id)
        {
            bool IsExist = true;
            string sql = " select * from AntiFleeing_Distributor where  ViewDel=0 and EnterpriseId=" + enterId + " ";
            if (id != 0) sql += " and Id!=" + id;

            if (!string.IsNullOrEmpty(name))
                sql += " and  Name='" + name + "'";
            if (!string.IsNullOrEmpty(tel))
                sql += " and ContactMobile='" + tel + "'";
            var model = _baseRepository.ExecuteText<AntiFleeing_Distributor>(sql);
            if (model != null) IsExist = false;
            return IsExist;

        }
        public int DiscAdd(L.EntityModel.AntiFleeing_Distributor model)
        {
            return baseRepository2.Insert<L.EntityModel.AntiFleeing_Distributor>(model);
        }
        public int DiscUpdate(L.EntityModel.AntiFleeing_Distributor model,int type=0)
        {
            if (type==1)
            {
                baseRepository2.Update<L.EntityModel.Sys_User>(p => p.Distributor == model.Id, p =>
                {
                    p.ViewDel = 1;
                });
                //string sql = "update sys_user set ViewDel=1 where  distributor="+model.Id;
                // _baseRepository.ExecuteSql<int>(sql);
            }
            return baseRepository2.Update<L.EntityModel.AntiFleeing_Distributor>(p => p.Id == model.Id, p =>
            {
                p.ViewDel = 1;
            });
        }
        public AntiFleeing_Distributor DiscGetModel(string id)
        {
            string sql = " SELECT * FROM ANTIFLEEING_DISTRIBUTOR WHERE ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<AntiFleeing_Distributor>(sql, parameters);
        }
        public V_Distributor DiscGetInfo(string id)
        {
            string sql = " SELECT * FROM V_DISTRIBUTOR WHERE ID=@ID";
            MySqlParameter[] parameters = {
                             new MySqlParameter("@ID",int.Parse(id))
                             };
            return _baseRepository.ExecuteText<V_Distributor>(sql, parameters);
        }
        #endregion

        #region 经销商等级
        public L.EntityModel.AntiFleeing_DistributorLevel GetLevelModelId(int id)
        {

            return baseRepository2.SearchFirstOrDefault<L.EntityModel.AntiFleeing_DistributorLevel>(p => p.Id == id);
            #region MyRegion
            //string sql = "select * from AntiFleeing_DistributorLevel where ID=" + id;
            //var result = _baseRepository2.Update(sql);
            //return result; 
            #endregion
        }
        public PagedList<AntiFleeing_DistributorLevel> GetDealerLevelService(ref PagerModel page)
        {

            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("");
            strSql1.Append("ID,");
            strSql1.Append("Level,");
            strSql1.Append("LevelName,");
            strSql1.Append("ChangeDate,");
            var strSql = new StringBuilder(strSql1.ToString().Trim(','));
            page.FldName = strSql.ToString();
            page.TblName = " AntiFleeing_DistributorLevel ";

            string StrCondition = " ViewDel=0  ";

            StrCondition += page.StrCondition;

            page.StrCondition = StrCondition;


            if (string.IsNullOrEmpty(page.FldSort) || page.FldSort == "")
                page.FldSort = "  Level asc ";

            MySqlParameter[] parameters = _baseRepository.SqParameterRefactor(ref page);
            IEnumerable<AntiFleeing_DistributorLevel> resultList = _baseRepository.ExecuteStoredProcedureCollection<AntiFleeing_DistributorLevel>("Common_PageList", parameters);
            return new PagedList<AntiFleeing_DistributorLevel>(resultList.ToList(), page.Page, page.PageSize, 1, Convert.ToInt32(parameters[6].Value));
        }

        public int AddLevel(AntiFleeing_DistributorLevel model)
        {
            int rulset = _baseRepository.Insert<AntiFleeing_DistributorLevel>(model);
            return rulset;
        }

        public int EditLeavel(AntiFleeing_DistributorLevel model)
        {
            return baseRepository2.Update<L.EntityModel.AntiFleeing_DistributorLevel>(p => p.Id == model.Id, p =>
            {
                p.LevelName = model.LevelName;
                p.ViewDel = model.ViewDel;
                p.Sequence = model.Sequence;
                p.EnterpriseId = model.EnterpriseId;
                p.CreateLoginID = model.CreateLoginID;
                p.CreateLoginName = model.CreateLoginName;             
                p.Level = model.Level;

            });
            #region MyRegion
            //return _baseRepository.Update<AntiFleeing_DistributorLevel>(model);
             #endregion
        }

        public int DelDealerLevelService(int id)
        {
            //AntiFleeing_DistributorLevel de = new AntiFleeing_DistributorLevel();

            //de.ViewDel = 1;
            //de.Id = id;
            //de.ChangeDate = DateTime.Now;
            //return _baseRepository.Update<AntiFleeing_DistributorLevel>(de);
            try
            {
                var _connectionString = ConfigurationManager.ConnectionStrings["mgj_basemysql"].ConnectionString;
                using (MySqlConnection conn = new MySqlConnection(_connectionString))
                {
                    using (MySqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("SELECT ID FROM antifleeing_distributor WHERE   ViewDel = 0 AND LevelID ={0}",id);
                        object _id = cmd.ExecuteScalar();
                        if (_id != null)
                        {
                            return 4445;//已被使用，无法删除
                        }
        

                        cmd.CommandText = string.Format("UPDATE AntiFleeing_DistributorLevel  SET ViewDel = 1  WHERE ID ={0}", id);
                        cmd.ExecuteScalar();
                        return 0000;
                    }
                }
            }
            catch { return 4444; }
        }


        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public IEnumerable<AntiFleeing_DistributorLevel> GetAllLevel(string key)
        {
            var resule = "select ID, LevelName, ViewDel, Sequence, EnterpriseId,CreateLoginName, CreateDate, ChangeLoginID, ChangeLoginName, ChangeDate, Level from AntiFleeing_DistributorLevel ";
            if (!string.IsNullOrWhiteSpace(key))
            {
                resule += " where  id In(" + key + ")";
            }
            return _baseRepository.ExecuteSqlCollection<AntiFleeing_DistributorLevel>(resule);
        }
        #endregion
        public string GetAddreId(string name)
        {
            string sql = "select codeId from sys_region where name like '%" + name + "%' LIMIT 1";
            sys_region model = _baseRepository.ExecuteText<sys_region>(sql);
            if (model != null)
            {
                return model.codeID;
            }
            else return "0";
        }   
        public int GetSalesAreaID(string name, int enterId)
        {
            string sql = "select id from antifleeing_salesarea where name='" + name + "' and EnterpriseId=" + enterId + " LIMIT 1";
            AntiFleeing_SalesArea model = _baseRepository.ExecuteText<AntiFleeing_SalesArea>(sql);
            if (model != null)
            {
                return model.ID;
            }
            else return 0;
        }
        public void ImportDic(string strSql)
        {
            // string sql = "insert into AntiFleeing_Distributor(`Name`,`Address`,`Contacts`,`LevelID`,`SalesAreaID`,`EnterpriseId`,`ViewDel`,`CreateLoginID`,`CreateLoginName`,`CreateDate`,`ContactMobile`,`JurisdictionPrivanceName`,`JurisdictionCity`,`JurisdictionCityName`,`JurisdictionPrivance`,`IsCrossArea`,`ChangeLoginID`,`ChangeLoginName`,`ChangeDate`,`ParentId`)  values " + strSql;
            _baseRepository.ExecuteSql<int>(strSql);
        }
        public string BatchImport_Dic_Excel() {
            MySqlParameter[] parameter = { new MySqlParameter("str", MySqlDbType.VarChar) };
            parameter[0].Direction = ParameterDirection.Output;
            _baseRepository.ExecuteStoredProcedure<string>("Proc_Distributor_Execel", parameter);
            return  parameter[0].Value.ToString();
        }
    }

}


